﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace WebApplication10.App_Code
{
    public class JobSeeker
    {
        public int JobSeekerID { get; set; }
        public string NameOfSeeker { get; set; }
        public int Contact { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public int PostalCode { get; set; }
        public char Gender { get; set; }
        public int DayOfBirth { get; set; }
        public int MonthOfBirth { get; set; }
        public int YearOfBirth { get; set; }
        public int JobID { get; set; }
        public int ResumeID { get; set; }
        public int CoverLetterID { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }

        
        public static JobSeeker GetJobSeekerByID(string JobSeekerID) 
        {
            SqlConnection conn = new SqlConnection();
            JobSeeker js = null;
            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from JobSeeker where JobSeekerID=@JobSeekerID";
                comm.Parameters.AddWithValue("@JobSeekerID", JobSeekerID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    js = new JobSeeker();
                    js.JobSeekerID = (int)dr["JobSeekerID"];
                    js.NameOfSeeker = (string)dr["NameOfSeeker"];
                    js.Contact = (int)dr["Contact"];
                    js.Email = (string)dr["Email"];
                    js.Address = (string)dr["Address"];
                    js.PostalCode = (int)dr["PostalCode"];
                    js.Gender = (char)dr["Gender"];
                    js.DayOfBirth = (int)dr["DayOfBirth"];
                    js.MonthOfBirth = (int)dr["MonthOfBirth"];
                    js.YearOfBirth = (int)dr["YearOfBirth"];
                    js.JobID = (int)dr["JobID"];
                    js.ResumeID = (int)dr["ResumeID"];
                    js.CoverLetterID = (int)dr["CoverLetterID"];
                    js.Username = (string)dr["Username"];
                    js.Password = (string)dr["Password"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return js;
        }

        public static JobSeeker GetJobSeekerByUsername(string Username)
        {
            SqlConnection conn = new SqlConnection();
            JobSeeker js = null;
            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from JobSeeker where Username=@Username";
                comm.Parameters.AddWithValue("@Username", Username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    js = new JobSeeker();
                    js.JobSeekerID = (int)dr["JobSeekerID"];
                    //js.NameOfSeeker = (string)dr["NameOfSeeker"];
                    //js.Contact = (int)dr["Contact"];
                    //js.Email = (string)dr["Email"];
                    //js.Address = (string)dr["Address"];
                    //js.PostalCode = (int)dr["PostalCode"];
                    //js.Gender = (char)dr["Gender"];
                    //js.DayOfBirth = (int)dr["DayOfBirth"];
                    //js.MonthOfBirth = (int)dr["MonthOfBirth"];
                    //js.YearOfBirth = (int)dr["YearOfBirth"];
                    //js.JobID = (int)dr["JobID"];
                    //js.ResumeID = (int)dr["ResumeID"];
                    //js.CoverLetterID = (int)dr["CoverLetterID"];
                    js.Username = (string)dr["Username"];
                    js.Password = (string)dr["Password"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return js;
        }

        public static void UpdateJobSeeker(JobSeeker js)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Update JobSeeker SET NameOfSeeker=@NameOfSeeker, Contact=@Contact, Email=@Email, Address=@Address, PostalCode=@PostalCode, Gender=@Gender, DayOfBirth=@DayOfBirth, MonthOfBirth=@MonthOfBirth, YearOfBirth=@YearOfBirth, JobID=@JobID, ResumeID=@ResumeID, CoverLetterID=@CoverLetterID, Username=@Username, Password=@Password WHERE JobSeekerID=@JobSeekerID";
                comm.Parameters.AddWithValue("@NameOfSeeker", js.NameOfSeeker);
                comm.Parameters.AddWithValue("@Contact", js.Contact);
                comm.Parameters.AddWithValue("@Email", js.Email);
                comm.Parameters.AddWithValue("@Address", js.Address);
                comm.Parameters.AddWithValue("@PostalCode", js.PostalCode);
                comm.Parameters.AddWithValue("@Gender", js.Gender);
                comm.Parameters.AddWithValue("@DayOfBirth", js.DayOfBirth);
                comm.Parameters.AddWithValue("@MonthOfBirth", js.MonthOfBirth);
                comm.Parameters.AddWithValue("@YearOfBirth", js.YearOfBirth);
                comm.Parameters.AddWithValue("@JobID", js.JobID);
                comm.Parameters.AddWithValue("@ResumeID", js.ResumeID);
                comm.Parameters.AddWithValue("@CoverLetterID", js.CoverLetterID);
                comm.Parameters.AddWithValue("@Username", js.Username);
                comm.Parameters.AddWithValue("@Password", js.Password);
                comm.Parameters.AddWithValue("@JobSeekerID", js.JobSeekerID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteJobSeeker(JobSeeker js) 
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Delete from JobSeeker WHERE JobSeekerID=@JobSeekerID";
                comm.Parameters.AddWithValue("@JobSeekerID", js.JobSeekerID);
                int rowsAffected = comm.ExecuteNonQuery(); ;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static int InsertJobSeeker(JobSeeker js) 
        {
            SqlConnection conn = new SqlConnection();
            int newJobSeekerID = 0;

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into JobSeeker (NameOfSeeker, Contact, Email, Address, PostalCode, Gender, DayOfBirth, MonthOfBirth, YearOfBirth, JobID, ResumeID, CoverLetterID, Username, Password) VALUES (@NameOfSeeker, @Contact, @Email, @Address, @PostalCode, @Gender, @DayOfBirth, @MonthOfBirth, @YearOfBirth, @JobID, @ResumeID, @CoverLetterID, @Username, @Password)";
                comm.Parameters.AddWithValue("@NameOfSeeker", js.NameOfSeeker);
                comm.Parameters.AddWithValue("@Contact", js.Contact);
                comm.Parameters.AddWithValue("@Email", js.Email);
                comm.Parameters.AddWithValue("@Address", js.Address);
                comm.Parameters.AddWithValue("@PostalCode", js.PostalCode);
                comm.Parameters.AddWithValue("@Gender", js.Gender);
                comm.Parameters.AddWithValue("@DayOfBirth", js.DayOfBirth);
                comm.Parameters.AddWithValue("@MonthOfBirth", js.MonthOfBirth);
                comm.Parameters.AddWithValue("@YearOfBirth", js.YearOfBirth);
                comm.Parameters.AddWithValue("@JobID", js.JobID);
                comm.Parameters.AddWithValue("@ResumeID", js.ResumeID);
                comm.Parameters.AddWithValue("@CoverLetterID", js.CoverLetterID);
                comm.Parameters.AddWithValue("@Username", js.Username);
                comm.Parameters.AddWithValue("@Password", js.Password);
                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newJobSeekerID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newJobSeekerID = int.Parse(dr["NewJobSeekerID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newJobSeekerID;
        }
    }
}
